﻿CREATE FUNCTION [dbo].[LoanBreakDown](
	@PmtsPerYear tinyint,@OpenDate smalldatetime,@FstDueDate smalldatetime,@DayCountConvention int
)RETURNS @T TABLE (PeriodsToFstPmt int,OddDaysToFstPmt int,OddDaysFactor numeric(18,10))
WITH SCHEMABINDING 
BEGIN

DECLARE @SP int SET @SP = dbo.BaseDays(@FstDueDate, @DayCountConvention)/@PmtsPerYear --Standard Period
INSERT INTO @T SELECT Periods,OddDays,CONVERT(numeric(18,10),OddDays)/@SP FROM dbo.PaymentsApart(@OpenDate,@FstDueDate,@PmtsPerYear)
RETURN
DECLARE @FNP int/*No Periods to First Pmt*/
DECLARE @MA int /*months apart*/ SET @MA = dbo.MonthsApart(@OpenDate,@FstDueDate)
SET @FNP = 
		CASE @PmtsPerYear
			 WHEN 12 THEN @MA
			 WHEN 24 THEN
				 @MA * 2 + CASE WHEN DATEDIFF(dd,DATEADD(mm,@MA,@OpenDate),@FstDueDate)> 15 THEN 1 ELSE 0 END
			 WHEN 26 THEN DATEDIFF(wk,@OpenDate,@FstDueDate)/2
			 WHEN 52 THEN DATEDIFF(wk,@OpenDate,@FstDueDate)
		END

DECLARE @FPD datetime
	SET @FPD = 
		CASE @PmtsPerYear
			 WHEN 12 THEN DATEADD(mm,@FNP,@OpenDate)
			 WHEN 24 THEN DATEADD(dd,15*(@FNP%2),DATEADD(mm,@FNP/2,@OpenDate))
			 WHEN 26 THEN DATEADD(wk,2*@FNP,@OpenDate)
			 WHEN 52 THEN DATEADD(wk,@FNP,@OpenDate)
		END

DECLARE @FND  numeric(32,30)/*No Odd Days to First Pmt*/SET @FND = DATEDIFF(dd,@FPD,@FstDueDate)
DECLARE @ODF  numeric(32,30)/*Odd Days Factor*/SET @ODF = @FND/@SP
INSERT INTO @T VALUES(/*@PMT,@FC,*/@FNP,@FND,@ODF)
RETURN
END



